热门标签 | HotTags
当前位置:  开发笔记 > 编程语言 > 正文

SparkSQL|窗口函数

窗口函数的定义引用一个大佬的定义:awindowfunctioncalculatesareturnvalueforeveryinputrowofatablebasedonagrou

窗口函数的定义引用一个大佬的定义: a window function calculates a return value for every input row of a table based on a group of rows。窗口函数与与其他函数的区别:



  • 普通函数: 作用于每一条记录,计算出一个新列(记录数不变);

  • 聚合函数: 作用于一组记录(全部数据按照某种方式分为多组),计算出一个聚合值(记录数变小);

  • 窗口函数: 作用于每一条记录,逐条记录去指定多条记录来计算一个值(记录数不变)。

窗口函数语法结构: 函数名(参数)OVER(PARTITION BY 子句 ORDER BY 子句 ROWS/RANGE子句)



  • 函数名:

  • OVER: 关键字,说明这是窗口函数,不是普通的聚合函数;

  • 子句

    • PARTITION BY: 分组字段

    • ORDER BY: 排序字段

    • ROWS/RANG窗口子句: 用于控制窗口的尺寸边界,有两种(ROW,RANGE)

      • ROW: 物理窗口,数据筛选基于排序后的index

      • RANGE: 逻辑窗口,数据筛选基于值





主要有以下三种窗口函数



  • ranking functions

  • analytic functions

  • aggregate functions


数据加载

from pyspark.sql.types import *schema = StructType().add('name', StringType(), True).add('department', StringType(), True).add('salary', IntegerType(), True)
df = spark.createDataFrame([("Tom", "Sales", 4500),("Georgi", "Sales", 4200),("Kyoichi", "Sales", 3000), ("Berni", "Sales", 4700),("Guoxiang", "Sales", 4200), ("Parto", "Finance", 2700),("Anneke", "Finance", 3300),("Sumant", "Finance", 3900),("Jeff", "Marketing", 3100),("Patricio", "Marketing", 2500)
], schema=schema)
df.createOrReplaceTempView('salary')
df.show()

+--------+----------+------+
| name|department|salary|
+--------+----------+------+
| Tom| Sales| 4500|
| Georgi| Sales| 4200|
| Kyoichi| Sales| 3000|
| Berni| Sales| 4700|
|Guoxiang| Sales| 4200|
| Parto| Finance| 2700|
| Anneke| Finance| 3300|
| Sumant| Finance| 3900|
| Jeff| Marketing| 3100|
|Patricio| Marketing| 2500|
+--------+----------+------+

ranking functions




































sqlDataFrame功能
row_numberrowNumber从1~n的唯一序号值
rankrank与denseRank一样,都是排名,对于相同的数值,排名一致。区别:rank不会跳过并列的排名
dense_rankdenseRank同rank
percent_rankpercentRank计算公式: (组内排名-1)/(组内行数-1),如果组内只有1行,则结果为0
ntilentile将组内数据排序后,按照指定的n切分为n个桶,该值为当前行的桶号(桶号从1开始)

spark.sql("""
SELECTname ,department,salary,row_number() over(partition by department order by salary) as index,rank() over(partition by department order by salary) as rank,dense_rank() over(partition by department order by salary) as dense_rank,percent_rank() over(partition by department order by salary) as percent_rank,ntile(2) over(partition by department order by salary) as ntile
FROM salary
"""
).toPandas()




namedepartmentsalaryindexrankdense_rankpercent_rankntile
0PatricioMarketing25001110.001
1JeffMarketing31002221.002
2KyoichiSales30001110.001
3GeorgiSales42002220.251
4GuoxiangSales42003220.251
5TomSales45004430.752
6BerniSales47005541.002
7PartoFinance27001110.001
8AnnekeFinance33002220.501
9SumantFinance39003331.002

analytic functions

























sqlDataFrame功能
cume_distcumeDist计算公式: 组内小于等于值当前行数/组内总行数
laglaglag(input, [offset,[default]]) 当前index
leadlead与lag相反

spark.sql("""
SELECTname ,department,salary,row_number() over(partition by department order by salary) as index,cume_dist() over(partition by department order by salary) as cume_dist,lag('salary', 2) over(partition by department order by salary) as lag,lead('salary', 2) over(partition by department order by salary) as lead FROM salary
"""
).toPandas()




namedepartmentsalaryindexcume_distlaglead
0PatricioMarketing250010.500000NoneNone
1JeffMarketing310021.000000NoneNone
2KyoichiSales300010.200000Nonesalary
3GeorgiSales420020.600000Nonesalary
4GuoxiangSales420030.600000salarysalary
5TomSales450040.800000salaryNone
6BerniSales470051.000000salaryNone
7PartoFinance270010.333333Nonesalary
8AnnekeFinance330020.666667NoneNone
9SumantFinance390031.000000salaryNone

aggregate functions

只是在一定窗口里实现一些普通的聚合函数。


























sql功能
avg平均值
sum求和
min最小值
max最大值

spark.sql("""
SELECTname ,department,salary,row_number() over(partition by department order by salary) as index,sum(salary) over(partition by department order by salary) as sum,avg(salary) over(partition by department order by salary) as avg,min(salary) over(partition by department order by salary) as min,max(salary) over(partition by department order by salary) as max
FROM salary
"""
).toPandas()




namedepartmentsalaryindexsumavgminmax
0PatricioMarketing2500125002500.025002500
1JeffMarketing3100256002800.025003100
2KyoichiSales3000130003000.030003000
3GeorgiSales42002114003800.030004200
4GuoxiangSales42003114003800.030004200
5TomSales45004159003975.030004500
6BerniSales47005206004120.030004700
7PartoFinance2700127002700.027002700
8AnnekeFinance3300260003000.027003300
9SumantFinance3900399003300.027003900

窗口子句

ROWS/RANG窗口子句: 用于控制窗口的尺寸边界,有两种(ROW,RANGE)



  • ROWS: 物理窗口,数据筛选基于排序后的index

  • RANGE: 逻辑窗口,数据筛选基于值

语法:OVER (PARTITION BY … ORDER BY … frame_type BETWEEN start AND end)

有以下5种边界



  • CURRENT ROW:

  • UNBOUNDED PRECEDING: 分区第一行

  • UNBOUNDED FOLLOWING: 分区最后一行

  • n PRECEDING: 前n行

  • n FOLLOWING: 后n行

  • UNBOUNDED: 起点

spark.sql("""
SELECTname ,department,salary,row_number() over(partition by department order by salary) as index,row_number() over(partition by department order by salary rows between UNBOUNDED PRECEDING and CURRENT ROW) as index1
FROM salary
"""
).toPandas()




namedepartmentsalaryindexindex1
0PatricioMarketing250011
1JeffMarketing310022
2KyoichiSales300011
3GeorgiSales420022
4GuoxiangSales420033
5TomSales450044
6BerniSales470055
7PartoFinance270011
8AnnekeFinance330022
9SumantFinance390033

混合应用

spark.sql("""
SELECTname ,department,salary,row_number() over(partition by department order by salary) as index,salary - (min(salary) over(partition by department order by salary)) as salary_diff
FROM salary
"""
).toPandas()




namedepartmentsalaryindexsalary_diff
0PatricioMarketing250010
1JeffMarketing31002600
2KyoichiSales300010
3GeorgiSales420021200
4GuoxiangSales420031200
5TomSales450041500
6BerniSales470051700
7PartoFinance270010
8AnnekeFinance33002600
9SumantFinance390031200

参考



  • Introducing Window Functions in Spark SQL

  • Standard Functions for Window Aggregation (Window Functions

  • List Of Spark SQL Window Functions

  • 在hive、Spark SQL中引入窗口函数



推荐阅读
  • 本文介绍了如何在iOS平台上使用GLSL着色器将YV12格式的视频帧数据转换为RGB格式,并展示了转换后的图像效果。通过详细的技术实现步骤和代码示例,读者可以轻松掌握这一过程,适用于需要进行视频处理的应用开发。 ... [详细]
  • 在 Vue 应用开发中,页面状态管理和跨页面数据传递是常见需求。本文将详细介绍 Vue Router 提供的两种有效方式,帮助开发者高效地实现页面间的数据交互与状态同步,同时分享一些最佳实践和注意事项。 ... [详细]
  • 在尝试对 QQmlPropertyMap 类进行测试驱动开发时,发现其派生类中无法正常调用槽函数或 Q_INVOKABLE 方法。这可能是由于 QQmlPropertyMap 的内部实现机制导致的,需要进一步研究以找到解决方案。 ... [详细]
  • PyTorch实用技巧汇总(持续更新中)
    空洞卷积(Dilated Convolutions)在卷积操作中通过在卷积核元素之间插入空格来扩大感受野,这一过程由超参数 dilation rate 控制。这种技术在保持参数数量不变的情况下,能够有效地捕捉更大范围的上下文信息,适用于多种视觉任务,如图像分割和目标检测。本文将详细介绍空洞卷积的计算原理及其应用场景。 ... [详细]
  • 本文介绍了一种自定义的Android圆形进度条视图,支持在进度条上显示数字,并在圆心位置展示文字内容。通过自定义绘图和组件组合的方式实现,详细展示了自定义View的开发流程和关键技术点。示例代码和效果展示将在文章末尾提供。 ... [详细]
  • 使用 ListView 浏览安卓系统中的回收站文件 ... [详细]
  • Web开发框架概览:Java与JavaScript技术及框架综述
    Web开发涉及服务器端和客户端的协同工作。在服务器端,Java是一种优秀的编程语言,适用于构建各种功能模块,如通过Servlet实现特定服务。客户端则主要依赖HTML进行内容展示,同时借助JavaScript增强交互性和动态效果。此外,现代Web开发还广泛使用各种框架和库,如Spring Boot、React和Vue.js,以提高开发效率和应用性能。 ... [详细]
  • 在处理 XML 数据时,如果需要解析 `` 标签的内容,可以采用 Pull 解析方法。Pull 解析是一种高效的 XML 解析方式,适用于流式数据处理。具体实现中,可以通过 Java 的 `XmlPullParser` 或其他类似的库来逐步读取和解析 XML 文档中的 `` 元素。这样不仅能够提高解析效率,还能减少内存占用。本文将详细介绍如何使用 Pull 解析方法来提取 `` 标签的内容,并提供一个示例代码,帮助开发者快速解决问题。 ... [详细]
  • 2018 HDU 多校联合第五场 G题:Glad You Game(线段树优化解法)
    题目链接:http://acm.hdu.edu.cn/showproblem.php?pid=6356在《Glad You Game》中,Steve 面临一个复杂的区间操作问题。该题可以通过线段树进行高效优化。具体来说,线段树能够快速处理区间更新和查询操作,从而大大提高了算法的效率。本文详细介绍了线段树的构建和维护方法,并给出了具体的代码实现,帮助读者更好地理解和应用这一数据结构。 ... [详细]
  • 如何使用mysql_nd:Python连接MySQL数据库的优雅指南
    无论是进行机器学习、Web开发还是爬虫项目,数据库操作都是必不可少的一环。本文将详细介绍如何使用Python通过 `mysql_nd` 库与 MySQL 数据库进行高效连接和数据交互。内容涵盖以下几个方面: ... [详细]
  • 本文探讨了利用Java实现WebSocket实时消息推送技术的方法。与传统的轮询、长连接或短连接等方案相比,WebSocket提供了一种更为高效和低延迟的双向通信机制。通过建立持久连接,服务器能够主动向客户端推送数据,从而实现真正的实时消息传递。此外,本文还介绍了WebSocket在实际应用中的优势和应用场景,并提供了详细的实现步骤和技术细节。 ... [详细]
  • 本文详细探讨了几种常用的Java后端开发框架组合及其具体应用场景。通过对比分析Spring Boot、MyBatis、Hibernate等框架的特点和优势,结合实际项目需求,为开发者提供了选择合适框架组合的参考依据。同时,文章还介绍了这些框架在微服务架构中的应用,帮助读者更好地理解和运用这些技术。 ... [详细]
  • 本文详细介绍了在MySQL中如何高效利用EXPLAIN命令进行查询优化。通过实例解析和步骤说明,文章旨在帮助读者深入理解EXPLAIN命令的工作原理及其在性能调优中的应用,内容通俗易懂且结构清晰,适合各水平的数据库管理员和技术人员参考学习。 ... [详细]
  • 本文介绍了UUID(通用唯一标识符)的概念及其在JavaScript中生成Java兼容UUID的代码实现与优化技巧。UUID是一个128位的唯一标识符,广泛应用于分布式系统中以确保唯一性。文章详细探讨了如何利用JavaScript生成符合Java标准的UUID,并提供了多种优化方法,以提高生成效率和兼容性。 ... [详细]
  • 本文深入解析了Python在处理HTML过滤时的实现方法及其应用场景。通过具体实例,详细介绍了如何利用Python代码去除HTML字符串中的标签和其他无关信息,确保内容的纯净与安全。此外,文章还探讨了该技术在网页抓取、数据清洗等领域的实际应用,为开发者提供了宝贵的参考。 ... [详细]
author-avatar
高振Andy
这个家伙很懒,什么也没留下!
PHP1.CN | 中国最专业的PHP中文社区 | DevBox开发工具箱 | json解析格式化 |PHP资讯 | PHP教程 | 数据库技术 | 服务器技术 | 前端开发技术 | PHP框架 | 开发工具 | 在线工具
Copyright © 1998 - 2020 PHP1.CN. All Rights Reserved | 京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有